﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
    public class ExcelReader
    {

        public static DataSet ReadExcel(string ExcelPath)
        {
            DataSet ds = new DataSet();
            string ExcelConnString = string.Empty;
            ExcelConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + ExcelPath + "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
            OleDbConnection oleDBConn = new OleDbConnection(ExcelConnString);
            string sSheetName = string.Empty;

            //if (ext == ".xls") xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(UploadDefaultPath + filename) + ";" + "Extended Properties=Excel 8.0;";
            //else if (ext == ".xlsx") xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath(UploadDefaultPath + filename) + ";" + "Extended Properties=Excel 8.0;";


            try
            {
                oleDBConn.Open();
                DataTable dtTablesList = oleDBConn.GetSchema("Tables");
                if (dtTablesList.Rows.Count > 0)
                {
                    sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
                }

                dtTablesList.Clear();
                dtTablesList.Dispose();

                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + sSheetName + "]", oleDBConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCmdSelect);
                objAdapter.Fill(ds);
                
            }

            catch (Exception ex)
            {
               
            }

            finally
            {
                if (oleDBConn != null)
                {
                    oleDBConn.Close();
                    oleDBConn.Dispose();
                }
            }

            return ds;
        }
    }
}
